﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.Text.RegularExpressions;
using System.Globalization;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Web.Script.Serialization;
using System.Web;
using System.Security.Cryptography;

namespace DoeObjects
{
    public class ImportSuggestion
    {
        public Loanable Loanable { get; set; }
        public List<Author> listAuthors { get; set; }
        public List<Topic> listTopics { get; set; }
        public string Authors { get; set; }
        public string Topics { get; set; }
        public string ISBNS { get; set; }
    }


    public class ImportReturn
    {
        public string ErrorMessage { get; set; }
        public ImportSuggestion Suggestion { get; set; }
        public Loanable ImportedLoanable { get; set; }
        public bool Imported { get; set; }
        public bool Error { get; set; }
        public bool APICallError { get; set; }
        public bool Conflict { get; set; }
        public bool ImportedWithNewID { get; set; }
        public bool DoeLibS_ID_changed { get; set; }
        public string DoeLibS_ID { get; set; }
    }


    public class Import
    {
        private const string URL = "https://www.googleapis.com/books/v1/volumes";
        private const string APIKEY = "&key=AIzaSyDWghC8zQlBkYRTDLL1clBRTmfM1sd9sOI";
        private static TextInfo myTI = new CultureInfo("en-US", false).TextInfo;

        /// <summary>
        /// Process the CSV row.
        /// </summary>
        public static async Task<ImportReturn> processRow(JArray row, int userID)
        {
            ImportReturn newReturn = new ImportReturn();

            if (row != null)
            {
                
                string isbnNr = Regex.Replace(row[0]["ISBN"].ToString(), "[-| ]", "");
                string[] multipleISBNS = isbnNr.Split(',');

                if(multipleISBNS.Length > 1)
                {
                    if(multipleISBNS[0].Length == 10 || multipleISBNS[0].Length == 13)
                    {
                        if(multipleISBNS[1].Length == 10 || multipleISBNS[1].Length == 13)
                            newReturn = await searchByISBN(multipleISBNS[0], row, userID, true);
                        else
                        {
                            newReturn = await searchByISBN(multipleISBNS[0], row, userID);
                            return newReturn;
                        }
                    }
                    else if (multipleISBNS[1].Length == 10 || multipleISBNS[1].Length == 13)
                    {
                        newReturn = await searchByISBN(multipleISBNS[0], row, userID);
                        return newReturn;
                    }

                    if (newReturn.Error == true)
                    {
                        newReturn = await searchByISBN(multipleISBNS[1], row, userID);
                        return newReturn;
                    }
                }

                else if (isbnNr != "")
                {
                    //ISBN PROVIDED
                    if (isbnNr.Length == 10 || isbnNr.Length == 13)
                    {
                        //ISBN HAS THE CORRECT LENGTH
                        newReturn = await searchByISBN(isbnNr, row, userID);

                        return newReturn;
                    }
                    else
                    {
                        //ISBN HAS THE WRONG LENGTH, USE TITLE INSTEAD
                        newReturn = await searchByTitle(row, userID);
                        
                        return newReturn;
                    }

                }
                else
                {
                    //ISBN NOT PROVIDED, USE TITLE INSTEAD
                    newReturn = await searchByTitle(row, userID);
                    
                    return newReturn;
                }
            }

           
            return newReturn;
        }
                
        /// <summary>
        /// Searches by the ISBN number. If it doesn't exist in the DB it will search on Google.
        /// </summary>
        public static async Task<ImportReturn> searchByISBN(string isbnNr, JArray row, int userID, bool multipleISBNS = false)
        {
            ImportReturn newReturn = new ImportReturn();

            //CHECK IF ISBN ALREADY EXISTS
            Title newTitle = null;
            if (isbnNr.Length == 10)
                newTitle = Title.getByIsbn10(isbnNr);
            else if (isbnNr.Length == 13)
                newTitle = Title.getByIsbn13(isbnNr);


            if(newTitle != null)
            {
                //ISBN EXISTS IN DB
                //if (matchDB(row, newTitle))
                //{
                    newReturn = addExistingToDB(newTitle, row, userID);
                    return newReturn;
                //}
                //else
                //{
                //    newReturn = createChangeSuggestion(row: row, userID: userID, existingTitle: newTitle);
                //    return newReturn;
                //}
            }
            else
            {
                //ISBN DOESN'T EXIST IN DB

                try
                {
                    using (HttpClient client = new HttpClient())
                    {
                        client.BaseAddress = new Uri(URL);
                        var jsonText = client.GetStringAsync("?q=isbn:" + isbnNr + "&fields=items(volumeInfo)" + APIKEY).Result;
                        var jss = new JavaScriptSerializer();
                        var googleRes = jss.Deserialize<dynamic>(jsonText);

                        if (googleRes.Count == 0)
                        {
                            //NO MATCH - SEARCH ON TITLE INSTEAD
                            if (!multipleISBNS)
                            {
                                newReturn = await searchByTitle(row, userID);
                                return newReturn;
                            }
                            else
                            {
                                newReturn.Error = true;
                                return newReturn;
                            }
                        }
                        else
                        {
                            //MATCH
                            //if (matchGoogle(row, googleRes))
                            //{
                                newReturn = addNewToDB(newTitle, row, googleRes, userID);
                                return newReturn;
                            //}
                            //else
                            //{
                            //    newReturn = createChangeSuggestion(row: row, userID: userID, googleRes: googleRes);
                            //    return newReturn;
                            //}
                        }
                    }
                }
                catch
                {
                    //SOMETHING WENT WRONG
                    newReturn.ErrorMessage = "Something went wrong when checking your data on Google Books. Try again.";
                    newReturn.APICallError = true;
                    newReturn.Error = true;
                    return newReturn;
                }
            }

        }
        
        /// <summary>
        /// Searches by the title. If it doesn't exist in the DB it will search on Google.
        /// </summary>
        public static async Task<ImportReturn> searchByTitle(JArray row, int userID)
        {
            //CHECK IF TITLE EXISTS IN DB
            ImportReturn newReturn = new ImportReturn();

            //IF MULTIPLE TITLES WITH THE SAME NAME ALREADY EXISTS?
            Title newTitle = null;
            List<Title> existingTitle = Title.getByTitle(row[0]["Title"].ToString(), true);

            if (existingTitle.Count > 0)
            {
                //TITLE EXISTS - ADD TO DB
                newTitle = existingTitle[0];
                if (matchDB(row, newTitle))
                {
                    newReturn = addExistingToDB(newTitle, row, userID);
                    return newReturn;
                }
                else
                {
                    newReturn = createChangeSuggestion(row: row, userID: userID, existingTitle: newTitle);
                    return newReturn;
                }
                
            }
            else
            {
                //TITLE DOESN'T EXIST

                try
                {
                    //TITLE DOESNT EXIST IN DB
                    using (HttpClient client = new HttpClient())
                    {
                        string longest = "";
                        string[] authSplit1 = Array.ConvertAll(row[0]["Authors"].ToString().Split(new Char[] { ',', '&' }), p => p.Trim());

                        string[] authSplit2 = authSplit1.First().Split(' ');
                        longest = authSplit2.OrderByDescending(s => s.Length).First();
                           

                        client.BaseAddress = new Uri(URL);
                        string extras = "";
                        if (longest == "")
                            extras = "&fields=items(volumeInfo)&maxResults=1";
                        else
                            extras = "+inauthor:" + longest + "&fields=items(volumeInfo)&maxResults=1";

                        var jsonText = client.GetStringAsync("?q=" + HttpUtility.UrlEncode(row[0]["Title"].ToString()) + extras + APIKEY).Result;
                        var jss = new JavaScriptSerializer();
                        var googleRes = jss.Deserialize<dynamic>(jsonText);

                        if (googleRes.Count == 0)
                        {
                            //NO MATCH ON GOOGLE
                            newReturn.ErrorMessage = "No match was found on Google Books or in DoeLibS. You can choose to use your own data.";
                            newReturn.Error = true;
                            return newReturn;
                        }
                        else
                        {
                            //ADD TITLE TO SUGGESTIONS
                            if (matchGoogle(row, googleRes))
                                newReturn = addNewToDB(newTitle, row, googleRes, userID);
                            else
                                newReturn = createChangeSuggestion(row: row, userID: userID, googleRes: googleRes);
                            return newReturn;
                        }
                    }
                }
                catch
                {
                    //SOMETHING WENT WRONG
                    newReturn.ErrorMessage = "Something went wrong when checking your data on Google Books. Try again.";
                    newReturn.APICallError = true;
                    newReturn.Error = true;
                    return newReturn;
                }

            }
        }

        /// <summary>
        /// The data didn't match. Create a suggestion.
        /// </summary>
        public static ImportReturn createChangeSuggestion(JArray row, int userID, dynamic googleRes = null, Title existingTitle = null)
        {
            ImportReturn newImpReturn = new ImportReturn();

            Loanable newLoanable = new Loanable();
            List<Author> listAuthors = new List<Author>();
            List<Topic> listTopics = new List<Topic>();

            if (existingTitle == null)
            {
                Title newTitle = new Title();
                var item = googleRes["items"][0];

                try
                {
                    //Add title and subtitle (if exists)
                    newTitle.BookTitle = item["volumeInfo"]["title"];
                    newTitle.BookTitle = newTitle.BookTitle + ": " + item["volumeInfo"]["subtitle"];
                }
                catch
                {}

                if(newTitle.BookTitle == null || newTitle.BookTitle == "")
                    newTitle.BookTitle = row[0]["Title"].ToString();


                try
                {
                    //Add published date
                    newTitle.EditionYear = Convert.ToInt32(item["volumeInfo"]["publishedDate"].Substring(0, 4));
                }
                catch
                {
                    int edYear;
                    Int32.TryParse(row[0]["Publication year"].ToString(), out edYear);
                    newTitle.EditionYear = edYear;
                }

                int edNr, firstEd;
                Int32.TryParse(row[0]["First edition year"].ToString(), out firstEd);
                Int32.TryParse(row[0]["Edition"].ToString(), out edNr);

                newTitle.EditionNumber = edNr;
                if (firstEd != 0)
                    newTitle.FirstEditionYear = firstEd;


                //Add isbn's
                try
                {
                    if (item["volumeInfo"]["industryIdentifiers"][0] != null && item["volumeInfo"]["industryIdentifiers"][0]["type"] == "ISBN_10")
                    {
                        newTitle.ISBN10 = item["volumeInfo"]["industryIdentifiers"][0]["identifier"];
                    }
                    else if (item["volumeInfo"]["industryIdentifiers"][0] != null && item["volumeInfo"]["industryIdentifiers"][0]["type"] == "ISBN_13")
                    {
                        newTitle.ISBN13 = item["volumeInfo"]["industryIdentifiers"][0]["identifier"];
                    }
                }
                catch
                {
                    string isbnNr = Regex.Replace(row[0]["ISBN"].ToString(), "[-| ]", "");
                    if (isbnNr.Length == 10)
                        newTitle.ISBN10 = isbnNr;
                    else if (isbnNr.Length == 13)
                        newTitle.ISBN13 = isbnNr;
                }
                try
                {
                    if (item["industryIdentifiers"][1] != null && item["industryIdentifiers"][1]["type"] == "ISBN_10")
                    {
                        newTitle.ISBN10 = item["industryIdentifiers"][1]["identifier"];
                    }
                    else if (item["industryIdentifiers"][1] != null && item["industryIdentifiers"][1]["type"] == "ISBN_13")
                    {
                        newTitle.ISBN13 = item["industryIdentifiers"][1]["identifier"];
                    }
                }
                catch
                {
                    string isbnNr = Regex.Replace(row[0]["ISBN"].ToString(), "[-| ]", "");
                    if (newTitle.ISBN10 == null && isbnNr.Length == 10)
                        newTitle.ISBN10 = isbnNr;
                    else if (newTitle.ISBN13 == null && isbnNr.Length == 13)
                        newTitle.ISBN13 = isbnNr;
                }

                //Publisher
                string _nameOfPublisher;
                try
                {
                    _nameOfPublisher = item["volumeInfo"]["publisher"];
                }
                catch
                {
                    _nameOfPublisher = row[0]["Publisher"].ToString();
                }

                List<Publisher> _publisher = Publisher.getByName(_nameOfPublisher, false);

                if (_publisher.Count == 0)
                {
                    Publisher newPub = new Publisher();
                    newPub.Name = _nameOfPublisher;
                    //newPub.PublisherId = Publisher.add(newPub);
                    newTitle.Publisher = newPub;
                }
                else
                {
                    newTitle.Publisher = _publisher[0];
                }

                //GET TOPICS AND ADD TO TITLE
                string[] _topicsFromFile = Array.ConvertAll(row[0]["Topics"].ToString().TrimEnd('?', '.', ',').Split(new Char[] { ',', '&' }), p => p.Trim());
                foreach (string topic in _topicsFromFile)
                {
                    string topicName = topic.Trim();
                    if (topicName != "" && topicName != " ")
                    {
                        List<Topic> _existingTopics = Topic.getByName(topicName, false);
                        if (_existingTopics.Count == 0)
                        {
                            Topic _newTopic = new Topic();
                            _newTopic.TopicName = topicName;
                            listTopics.Add(_newTopic);
                        }
                        else
                        {
                            listTopics.Add(_existingTopics[0]);
                        }
                    }
                }


                //ADD AUTHORS
                bool allAreEds = false;
                dynamic _authors = null;

                try
                {
                    _authors = item["volumeInfo"]["authors"];
                }
                catch
                {
                    if (Regex.IsMatch(row[0]["Authors"].ToString(), @"\(Eds\)"))
                        allAreEds = true;
                    string authors = Regex.Replace(row[0]["Authors"].ToString(), @"\(Eds\)", "");

                    string[] _authorsFromFile = Array.ConvertAll(authors.Split(new Char[] { ',', '&' }), p => p.Trim());
                    List<string> _names = new List<string>();
                    foreach (string name in _authorsFromFile)
                    {
                        _names.Add(myTI.ToTitleCase(name.ToLower()));
                    }
                    _authors = _names;

                }

                
                if (_authors != null)
                {
                    foreach (string auth in _authors)
                    {
                        string name = auth;
                        bool authIsEd = false;

                        if (!allAreEds)
                        {
                            if (Regex.IsMatch(name, @"\(Ed\)"))
                            {
                                name = Regex.Replace(name, @"\(Ed\)", "");
                                authIsEd = true;
                            }
                        }
                        else
                            name = Regex.Replace(name, @"\(Ed\)", "");

                        List<Author> _existingAuthor = Author.getByName(name);
                        if (_existingAuthor.Count == 0)
                        {
                            Author _newAuthor = new Author();
                            _newAuthor.Name = name;

                            if (allAreEds)
                                _newAuthor.isEditor = true;
                            else if (authIsEd)
                                _newAuthor.isEditor = true;
                            else
                                _newAuthor.isEditor = false;

                            listAuthors.Add(_newAuthor);
                        }
                        else
                        {
                            Author _newAuthor = new Author();
                            _newAuthor = _existingAuthor[0];

                            if (allAreEds)
                                _newAuthor.isEditor = true;
                            else if (authIsEd)
                                _newAuthor.isEditor = true;
                            else
                                _newAuthor.isEditor = false;

                            listAuthors.Add(_newAuthor);
                        }
                    }
                }

                newLoanable.Title = newTitle;

            }
            else
            {
                newLoanable.Title = existingTitle;

                listAuthors = Author.getAuthorsFromBook(existingTitle.TitleId);
                listTopics = Topic.getTopicsFromBook(existingTitle.TitleId);
            }


            
            //User
            User owner = User.getUserByUserId(userID);
            newLoanable.Owner = owner;

            if (row[0]["Room"].ToString() != "")
                newLoanable.Location = row[0]["Room"].ToString();
            else
                newLoanable.Location = owner.Room;

            LoanableCategory Category = null;
            LoanableCategory existingCat = LoanableCategory.getByName(row[0]["Location category"].ToString(), userID);
            if (existingCat == null)
            {
                //NEW LOCATION CATEGORY
                Category = new LoanableCategory();
                Category.Owner = owner;
                Category.Name = row[0]["Location category"].ToString();
                newLoanable.Category = Category;
            }
            else
            {
                //CATEGORY EXISTS
                Category = existingCat;
                newLoanable.Category = Category;
            }

            string CSVDOEID = Regex.Replace(row[0]["Doelibs Identifier"].ToString(), " ", "");
            if (CSVDOEID != "")
            {
                if (Loanable.uniqueDoeLibSIdentifier(CSVDOEID))
                {
                    newLoanable.Barcode = CSVDOEID;
                    newImpReturn.DoeLibS_ID_changed = false;
                }
                else
                {
                    string newID = generateDoeLibSIdentifier();
                    while (!Loanable.uniqueDoeLibSIdentifier(newID))
                    {
                        newID = generateDoeLibSIdentifier();
                    }

                    newLoanable.Barcode = newID;
                    newImpReturn.DoeLibS_ID_changed = true;
                }
            }
            else
            {
                string newID = generateDoeLibSIdentifier();
                while (!Loanable.uniqueDoeLibSIdentifier(newID))
                {
                    newID = generateDoeLibSIdentifier();
                }

                newLoanable.Barcode = newID;
                newImpReturn.DoeLibS_ID_changed = true;
            }



            ImportSuggestion suggestion = new ImportSuggestion();
            suggestion.Loanable = newLoanable;

            string isbnToString = "";
            if (newLoanable.Title.ISBN10 != null)
                isbnToString += newLoanable.Title.ISBN10;
            if (newLoanable.Title.ISBN13 != null)
            {
                if (isbnToString == "")
                    isbnToString += newLoanable.Title.ISBN13;
                else
                    isbnToString += ", " + newLoanable.Title.ISBN13;
            }

            suggestion.ISBNS = isbnToString;

            string authorsToString = "";     
            for(int i = 0; i<listAuthors.Count; i++) {
                if (i != listAuthors.Count - 1)
                    authorsToString += listAuthors[i].Name + ", ";
                else
                    authorsToString += listAuthors[i].Name;
            }
            suggestion.Authors = authorsToString;


            string topicsToString = ""; 
            for (int i = 0; i < listTopics.Count; i++)
            {
                if (i != listTopics.Count - 1)
                    topicsToString += listTopics[i].TopicName + ", ";
                else
                    topicsToString += listTopics[i].TopicName;
            }
            suggestion.Topics = topicsToString;

            suggestion.listAuthors = listAuthors;
            suggestion.listTopics = listTopics;
            newImpReturn.Conflict = true;
            newImpReturn.Suggestion = suggestion;

            return newImpReturn;
        }

        /// <summary>
        /// Matches the data from the Google Books API with the data from the CSV file. Returns bool.
        /// </summary>
        public static bool matchGoogle(JArray row, dynamic googleRes)
        {
            var item = googleRes["items"][0]["volumeInfo"];

            //Match title
            if (row[0]["Title"].ToString() != "")
            {
                string google = "";                
                string CSV = row[0]["Title"].ToString();
                try
                {
                    google = item["title"];
                    google = google + ": " + item["subtitle"];
                }
                catch { }

                if(google.Length >= CSV.Length)
                {
                    CSV = Regex.Escape(CSV);
                    if (!Regex.IsMatch(google, CSV, RegexOptions.IgnoreCase))
                        return false;
                }
                else
                {
                    google = Regex.Escape(google);
                    if (!Regex.IsMatch(CSV, google, RegexOptions.IgnoreCase))
                        return false;
                }
                
            }

            //Match publication year
            if (row[0]["Publication year"].ToString() != "")
            {
                try
                {
                    if (row[0]["Publication year"].ToString() != item["publishedDate"].Substring(0, 4))
                        return false;
                }
                catch { }
            }

            //Match ISBN
            if (row[0]["ISBN"].ToString() != "")
            {
                string isbnNr = Regex.Replace(row[0]["ISBN"].ToString(), "[-| ]", "");
                if (isbnNr.Length == 10)
                {
                    try
                    {
                        if (item["industryIdentifiers"][0] != null && item["industryIdentifiers"][0]["type"] == "ISBN_10")
                        {
                            if (isbnNr != item["industryIdentifiers"][0]["identifier"])
                                return false;
                        }
                        else if (item["industryIdentifiers"][1] != null && item["industryIdentifiers"][1]["type"] == "ISBN_10")
                        {
                            if (isbnNr != item["industryIdentifiers"][1]["identifier"])
                                return false;
                        }
                    }
                    catch { }
                }
                else if (isbnNr.Length == 13)
                {
                    try
                    {
                        if (item["industryIdentifiers"][0] != null && item["industryIdentifiers"][0]["type"] == "ISBN_13")
                        {
                            if (isbnNr != item["industryIdentifiers"][0]["identifier"])
                                return false;
                        }
                        else if (item["industryIdentifiers"][1] != null && item["industryIdentifiers"][1]["type"] == "ISBN_13")
                        {
                            if (isbnNr != item["industryIdentifiers"][1]["identifier"])
                                return false;
                        }
                    }
                    catch { }
                }
            }


            //Match publisher
            if (row[0]["Publisher"].ToString() != "")
            {
                try
                {
                    string googlePub = item["publisher"];
                    if (!Regex.IsMatch(googlePub, row[0]["Publisher"].ToString(), RegexOptions.IgnoreCase))
                        return false;
                }
                catch { }
            }


            //Match authors
            if (row[0]["Authors"].ToString() != "")
            {
                dynamic googleAuthors = null;
                try
                {
                    googleAuthors = item["authors"];

                    string authorsLine = Regex.Replace(row[0]["Authors"].ToString(), @"\(Ed\)", "");
                    authorsLine = Regex.Replace(authorsLine, @"\(Eds\)", "");
                    string[] csvAuthors = Array.ConvertAll(authorsLine.Split(new Char[] { ',', '&' }), p => p.Trim());


                    bool authorMatch = false;
                    foreach (string auth in googleAuthors)
                    {
                        for (int i = 0; i < csvAuthors.Length; i++)
                        {
                            string[] csvCurrAuth = csvAuthors[i].Split(' ');
                            string longest = csvCurrAuth.OrderByDescending(s => s.Length).First();

                            if (Regex.IsMatch(auth, longest, RegexOptions.IgnoreCase))
                                authorMatch = true;
                        }
                    }

                    if (!authorMatch)
                        return false;

                }
                catch { }
            }

            return true;
        }
        
        /// <summary>
        /// Matches the data from the DB with the data from the CSV file. Returns bool.
        /// </summary>
        public static bool matchDB(JArray row, Title existingTitle)
        {
            //Match title
            string title = Regex.Escape(row[0]["Title"].ToString());
            if (!Regex.IsMatch(existingTitle.BookTitle, title, RegexOptions.IgnoreCase))
                return false;

            //Match ISBN
            if(row[0]["ISBN"].ToString() != "")
            {
                string isbnNr = Regex.Replace(row[0]["ISBN"].ToString(), "[-| ]", "");
                if (isbnNr.Length == 10)
                {
                    if(isbnNr != existingTitle.ISBN10)
                        return false;
                }
                else if (isbnNr.Length == 13)
                {
                    if(isbnNr != existingTitle.ISBN13)
                        return false;
                }
                else
                    return false;
            }

            //Match publication year
            if (row[0]["Publication year"].ToString() != "")
            {
                int parsedYear;
                Int32.TryParse(row[0]["Publication year"].ToString(), out parsedYear);
                if (parsedYear != existingTitle.EditionYear)
                    return false;
            }

            //Match publisher
            if (row[0]["Publisher"].ToString() != "")
            {

                if (!Regex.IsMatch(existingTitle.Publisher.Name, row[0]["Publisher"].ToString(), RegexOptions.IgnoreCase))
                    return false;
            }

            //Match authors
            if (row[0]["Authors"].ToString() != "")
            {
                string csvAuthorLine = Regex.Replace(row[0]["Authors"].ToString(), @"\(Ed\)", "");
                csvAuthorLine = Regex.Replace(csvAuthorLine, @"\(Eds\)", "");
                string[] csvAuthors = Array.ConvertAll(csvAuthorLine.Split(new Char[] { ',', '&' }), p => p.Trim());
                List<Author> listAuthors = Author.getAuthorsFromBook(existingTitle.TitleId);
                List<Author> listEditors = Author.getEditorsFromBook(existingTitle.TitleId);
                listAuthors.AddRange(listEditors);


                bool authorMatch = false;
                foreach(Author author in listAuthors)
                {
                    string authorName = author.Name;
                    for (int i = 0; i < csvAuthors.Length; i++)
                    {
                        string[] csvCurrAuth = csvAuthors[i].Split(' ');
                        string longest = csvCurrAuth.OrderByDescending(s => s.Length).First();

                        if (Regex.IsMatch(authorName, longest, RegexOptions.IgnoreCase))
                            authorMatch = true;
                    }
                }
                
                if (!authorMatch)
                    return false;
            }
           
            
            return true;
        }
        
        /// <summary>
        /// If existing Title exists and there are no conflicts, add to DB.
        /// </summary>
        public static ImportReturn addExistingToDB(Title existingTitle, JArray row, int userID)
        {
            ImportReturn newReturn = new ImportReturn();
            //ADD LOANABLE
            if(existingTitle != null)
            {
                Loanable newLoanable = new Loanable();

                //DoeLibS Identifier
                if (row[0]["Doelibs Identifier"].ToString() != "")
                {
                    if (Loanable.uniqueDoeLibSIdentifier(row[0]["Doelibs Identifier"].ToString()))
                    {
                        newLoanable.Barcode = row[0]["Doelibs Identifier"].ToString();
                        newReturn.ImportedWithNewID = false;
                        newReturn.DoeLibS_ID = newLoanable.Barcode;
                    }
                    else
                    {
                        string newID = generateDoeLibSIdentifier();
                        while (!Loanable.uniqueDoeLibSIdentifier(newID))
                        {
                            newID = generateDoeLibSIdentifier();
                        }

                        newLoanable.Barcode = newID;
                        newReturn.DoeLibS_ID = newLoanable.Barcode;
                        newReturn.ImportedWithNewID = true;
                    }
                }
                else
                {
                    string newID = generateDoeLibSIdentifier();
                    while (!Loanable.uniqueDoeLibSIdentifier(newID))
                    {
                        newID = generateDoeLibSIdentifier();
                    }

                    newLoanable.Barcode = newID;
                    newReturn.DoeLibS_ID = newLoanable.Barcode;
                    newReturn.ImportedWithNewID = true;
                }

                newLoanable.Title = existingTitle;
                User owner = User.getUserByUserId(userID);
                newLoanable.Owner = owner;

                if(row[0]["Room"].ToString() != "")
                    newLoanable.Location = row[0]["Room"].ToString();
                else
                    newLoanable.Location = owner.Room;

                LoanableCategory Category = null;
                LoanableCategory existingCat = LoanableCategory.getByName(row[0]["Location category"].ToString(), userID);
                if (existingCat == null)
                {
                    //NEW LOCATION CATEGORY
                    Category = new LoanableCategory();
                    Category.Owner = owner;
                    Category.Name = row[0]["Location category"].ToString();
                    newLoanable.Category = Category;
                    newLoanable.Category.CategoryId = LoanableCategory.add(Category);
                    newLoanable.LoanableId = Loanable.add(newLoanable);
                }
                else
                {
                    //CATEGORY EXISTS
                    Category = existingCat;
                    newLoanable.Category = Category;
                    newLoanable.LoanableId = Loanable.add(newLoanable);
                }

                newReturn.ImportedLoanable = newLoanable;
                newReturn.Imported = true;
                return newReturn;
            }
            return newReturn;
        }
                       
        /// <summary>
        /// If no Title exists and there are no conflicts, add to DB.
        /// </summary>
        public static ImportReturn addNewToDB(Title newTitle, JArray row, dynamic googleRes, int userID)
        {
            ImportReturn newReturn = new ImportReturn();

            //ADD TITLE + LOANABLE
            var item = googleRes["items"][0]["volumeInfo"];

            newTitle = new Title();
            try
            {
                //Add title and subtitle (if exists)
                newTitle.BookTitle = item["title"];
                newTitle.BookTitle = newTitle.BookTitle + ": " + item["subtitle"];
                if (newTitle.BookTitle.Length > 100)
                    newTitle.BookTitle = item["title"];
            }
            catch { }

            //Try parse edition values from file. 
            int _firstEdYear, _edNumber, _edYear;
            int.TryParse(row[0]["First edition year"].ToString(), out _firstEdYear);
            int.TryParse(row[0]["Edition"].ToString(), out _edNumber);
            int.TryParse(row[0]["Publication year"].ToString(), out _edYear);

            newTitle.FirstEditionYear = _firstEdYear;
            newTitle.EditionNumber = _edNumber;

            //If no edition year provided in file -> add from Google (if exists)
            if (_edYear == 0)
            {
                try
                {
                    newTitle.EditionYear = item["publishedDate"].Substring(0, 4);
                }
                catch { newTitle.EditionYear = _edYear; }
            }
            else
                newTitle.EditionYear = _edYear;


            //ISBN NUMBERS
            //Try to fetch from Google.
            try
            {
                if (item["industryIdentifiers"][0] != null && item["industryIdentifiers"][0]["type"] == "ISBN_10")
                {
                    newTitle.ISBN10 = item["industryIdentifiers"][0]["identifier"];
                }
                else if (item["industryIdentifiers"][0] != null && item["industryIdentifiers"][0]["type"] == "ISBN_13")
                {
                    newTitle.ISBN13 = item["industryIdentifiers"][0]["identifier"];
                }
            }
            catch
            {
                string isbnNr = Regex.Replace(row[0]["ISBN"].ToString(), "[-| ]", "");
                if (isbnNr.Length == 10)
                    newTitle.ISBN10 = isbnNr;
                else if (isbnNr.Length == 13)
                    newTitle.ISBN13 = isbnNr;
            }
            try
            {
                if (item["industryIdentifiers"][1] != null && item["industryIdentifiers"][1]["type"] == "ISBN_10")
                {
                    newTitle.ISBN10 = item["industryIdentifiers"][1]["identifier"];
                }
                else if (item["industryIdentifiers"][1] != null && item["industryIdentifiers"][1]["type"] == "ISBN_13")
                {
                    newTitle.ISBN13 = item["industryIdentifiers"][1]["identifier"];
                }
            }
            catch
            {
                string isbnNr = Regex.Replace(row[0]["ISBN"].ToString(), "[-| ]", "");
                if (newTitle.ISBN10 == null && isbnNr.Length == 10)
                    newTitle.ISBN10 = isbnNr;
                else if (newTitle.ISBN13 == null && isbnNr.Length == 13)
                    newTitle.ISBN13 = isbnNr;
            }



            //PUBLISHER
            //Try to fetch from Google, else fetch from file
            string _nameOfPublisher;
            try
            {
                _nameOfPublisher = item["publisher"];
            }
            catch
            {
                _nameOfPublisher = row[0]["Publisher"].ToString();
            }

            List<Publisher> _publisher = Publisher.getByName(_nameOfPublisher, false);

            if (_publisher.Count == 0)
            {
                Publisher newPub = new Publisher();
                newPub.Name = _nameOfPublisher;
                newPub.PublisherId = Publisher.add(newPub);
                newTitle.Publisher = newPub;
            }
            else
            {
                newTitle.Publisher = _publisher[0];
            }



            //ADD THE TITLE -> GET TITLE ID
            newTitle.TitleId = Title.add(newTitle);


            //GET TOPICS AND ADD TO TITLE
            string[] _topicsFromFile = Array.ConvertAll(row[0]["Topics"].ToString().Split(new Char[] { ',', '&' }), p => p.Trim());
            foreach (string topic in _topicsFromFile)
            {
                List<Topic> _existingTopics = Topic.getByName(topic, false);
                if (_existingTopics.Count == 0)
                {
                    Topic _newTopic = new Topic();
                    _newTopic.TopicName = topic;
                    _newTopic.TopicId = Topic.add(_newTopic);
                    Title.addTopic(_newTopic.TopicId, newTitle.TitleId);
                }
                else
                {
                    Title.addTopic(_existingTopics[0].TopicId, newTitle.TitleId);
                }
            }


            //ADD AUTHORS
            bool allAreEds = false;
            dynamic _authors = null;

            try
            {
                _authors = item["authors"];
            }
            catch
            {
                if (Regex.IsMatch(row[0]["Authors"].ToString(), @"\(Eds\)"))
                    allAreEds = true;
                string authors = Regex.Replace(row[0]["Authors"].ToString(), @"\(Eds\)", "");
                string[] _authorsFromFile = Array.ConvertAll(authors.Split(new Char[] { ',', '&' }), p => p.Trim());
                List<string> _names = new List<string>();
                foreach (string name in _authorsFromFile)
                {
                    _names.Add(myTI.ToTitleCase(name.ToLower()));
                }
                _authors = _names;

            }

            
            if (_authors != null)
            {                
                foreach (string auth in _authors)
                {
                    string name = auth;
                    bool authIsEd = false;

                    if (!allAreEds)
                    {
                        if (Regex.IsMatch(name, @"\(Ed\)"))
                        {
                            name = Regex.Replace(name, @"\(Ed\)", "");
                            authIsEd = true;
                        }
                    }
                    else
                        name = Regex.Replace(name, @"\(Ed\)", "");

                    List<Author> _existingAuthor = Author.getByName(name);
                    if (_existingAuthor.Count == 0)
                    {
                        Author _newAuthor = new Author();
                        _newAuthor.Name = name;
                        _newAuthor.Aid = Author.add(_newAuthor);

                        if(allAreEds)
                            Title.addAuthor(_newAuthor.Aid, newTitle.TitleId, true);
                        else if (authIsEd)
                            Title.addAuthor(_newAuthor.Aid, newTitle.TitleId, true);
                        else
                            Title.addAuthor(_newAuthor.Aid, newTitle.TitleId, false);

                    }
                    else
                    {
                        if (allAreEds)
                            Title.addAuthor(_existingAuthor[0].Aid, newTitle.TitleId, true);
                        else if (authIsEd)
                            Title.addAuthor(_existingAuthor[0].Aid, newTitle.TitleId, true);
                        else
                            Title.addAuthor(_existingAuthor[0].Aid, newTitle.TitleId, false);
                    }
                }
            }


            Loanable newLoanable = new Loanable();
            //DoeLibS Identifier
            if (row[0]["Doelibs Identifier"].ToString() != "")
            {
                if (Loanable.uniqueDoeLibSIdentifier(row[0]["Doelibs Identifier"].ToString()))
                {
                    newLoanable.Barcode = row[0]["Doelibs Identifier"].ToString();
                    newReturn.ImportedWithNewID = false;
                    newReturn.DoeLibS_ID = newLoanable.Barcode;
                }
                else
                {
                    string newID = generateDoeLibSIdentifier();
                    while (!Loanable.uniqueDoeLibSIdentifier(newID))
                    {
                        newID = generateDoeLibSIdentifier();
                    }

                    newLoanable.Barcode = newID;
                    newReturn.DoeLibS_ID = newLoanable.Barcode;
                    newReturn.ImportedWithNewID = true;
                }
            }
            else
            {
                string newID = generateDoeLibSIdentifier();
                while (!Loanable.uniqueDoeLibSIdentifier(newID))
                {
                    newID = generateDoeLibSIdentifier();
                }

                newLoanable.Barcode = newID;
                newReturn.DoeLibS_ID = newLoanable.Barcode;
                newReturn.ImportedWithNewID = true;
            }

            newLoanable.Title = newTitle;
            User owner = User.getUserByUserId(userID);
            newLoanable.Owner = owner;

            if (row[0]["Room"].ToString() != "")
                newLoanable.Location = row[0]["Room"].ToString();
            else
                newLoanable.Location = owner.Room;

            LoanableCategory Category = null;
            LoanableCategory existingCat = LoanableCategory.getByName(row[0]["Location category"].ToString(), userID);
            if (existingCat == null)
            {
                //NEW LOCATION CATEGORY
                Category = new LoanableCategory();
                Category.Owner = owner;
                Category.Name = row[0]["Location category"].ToString();
                newLoanable.Category = Category;
                newLoanable.Category.CategoryId = LoanableCategory.add(Category);
                newLoanable.LoanableId = Loanable.add(newLoanable);
            }
            else
            {
                //CATEGORY EXISTS
                Category = existingCat;
                newLoanable.Category = Category;
                newLoanable.LoanableId = Loanable.add(newLoanable);
            }

            newReturn.ImportedLoanable = newLoanable;
            newReturn.Imported = true;
            return newReturn;

        }

               
        /// <summary>
        /// If the DoeLibS Identifier is taken, generate a new one.
        /// </summary>
        private static string generateDoeLibSIdentifier()
        {
            RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
            var bytes = new Byte[3];
            rng.GetBytes(bytes);
            uint asInt = BitConverter.ToUInt16(bytes, 0);
            string test = asInt.ToString();
            return test;
        }



        public static async Task<ImportReturn> CSVToDB(JObject row, int userID)
        {
            ImportReturn newReturn = new ImportReturn();

            try
            {
                Loanable newLoanable = new Loanable();
                Title newTitle = new Title();

                if (row["Title"].ToString() != "")
                {
                    newTitle.BookTitle = row["Title"].ToString();
                }
                else
                {
                    newReturn.ErrorMessage = "You have not inserted a title.";
                    newReturn.Error = true;
                    return newReturn;
                }

                if (row["ISBN"].ToString() != null)
                {

                    string isbnNr = Regex.Replace(row["ISBN"].ToString(), "[-| ]", "");
                    string[] multipleISBNS = isbnNr.Split(',');

                    if(multipleISBNS.Length > 1)
                    {
                        if (multipleISBNS[0].Length == 10)
                            newTitle.ISBN10 = multipleISBNS[0];
                        else if (multipleISBNS[0].Length == 13)
                            newTitle.ISBN13 = multipleISBNS[0];

                        if (multipleISBNS[1].Length == 10)
                            newTitle.ISBN10 = multipleISBNS[1];
                        else if (multipleISBNS[1].Length == 13)
                            newTitle.ISBN13 = multipleISBNS[1];
                    }

                    else if (isbnNr != "")
                    {
                        if (isbnNr.Length == 10)
                        {
                            newTitle.ISBN10 = isbnNr;
                        }
                        else if (isbnNr.Length == 13)
                        {
                            newTitle.ISBN13 = isbnNr;
                        }
                        else
                        {
                            newReturn.ErrorMessage = "The ISBN number has to be 10 or 13 characters (not including hyphens or whitespaces).";
                            newReturn.Error = true;
                            return newReturn;
                        }
                    }
                }

                if (row["Publisher"].ToString() != null)
                {
                    string _nameOfPublisher = row["Publisher"].ToString();

                    List<Publisher> _publisher = Publisher.getByName(_nameOfPublisher, false);

                    if (_publisher.Count == 0)
                    {
                        Publisher newPub = new Publisher();
                        newPub.Name = _nameOfPublisher;
                        newPub.PublisherId = Publisher.add(newPub);
                        newTitle.Publisher = newPub;
                    }
                    else
                    {
                        newTitle.Publisher = _publisher[0];
                    }
                }

                int _firstEdYear, _edNumber, _edYear;
                int.TryParse(row["First edition year"].ToString(), out _firstEdYear);
                int.TryParse(row["Edition"].ToString(), out _edNumber);
                int.TryParse(row["Publication year"].ToString(), out _edYear);

                if (_firstEdYear != 0)
                {
                    newTitle.FirstEditionYear = _firstEdYear;
                }
                newTitle.EditionNumber = _edNumber;
                newTitle.EditionYear = _edYear;

                Title titleExists = null;
                List<Title> titleExists2 = null;
                if (newTitle.ISBN10 != null)
                    titleExists = Title.getByIsbn10(newTitle.ISBN10);
                if (titleExists == null && newTitle.ISBN13 != null)
                    titleExists = Title.getByIsbn13(newTitle.ISBN13);

                if (titleExists == null)
                {
                    titleExists2 = Title.getByTitle(newTitle.BookTitle, true);
                    if (titleExists2.Count > 0)
                    {
                        newTitle = titleExists2[0];
                    }

                    newTitle.TitleId = Title.add(newTitle);
                }
                else
                {
                    newTitle = titleExists;
                }
                //ADD TITLE
                newLoanable.Title = newTitle;

                User owner = User.getUserByUserId(userID);
                if (row["Location category"].ToString() != null)
                {
                    LoanableCategory Category = null;
                    LoanableCategory existingCat = LoanableCategory.getByName(row["Location category"].ToString(), userID);
                    if (existingCat == null)
                    {
                        //NEW LOCATION CATEGORY
                        Category = new LoanableCategory();
                        Category.Owner = owner;
                        Category.Name = row["Location category"].ToString();
                        newLoanable.Category = Category;
                        newLoanable.Category.CategoryId = LoanableCategory.add(Category);
                    }
                    else
                    {
                        //CATEGORY EXISTS
                        Category = existingCat;
                        newLoanable.Category = Category;
                    }
                }
                else
                {
                    newReturn.ErrorMessage = "You need to insert a location category.";
                    newReturn.Error = true;
                    return newReturn;
                }


                newLoanable.Owner = owner;
                if (row["Room"].ToString() != "")
                    newLoanable.Location = row["Room"].ToString();
                else
                    newLoanable.Location = owner.Room;

                List<Author> titleHasAuthors = Author.getAuthorsFromBook(newLoanable.Title.TitleId);
                if (titleHasAuthors.Count == 0)
                {
                    if (row["Authors"].ToString() != null)
                    {
                        bool allAreEds = false;

                        if (Regex.IsMatch(row["Authors"].ToString(), @"\(Eds\)"))
                            allAreEds = true;
                        string authors = Regex.Replace(row["Authors"].ToString(), @"\(Eds\)", "");
                        string[] _authorsFromFile = Array.ConvertAll(authors.Split(new Char[] { ',', '&' }), p => p.Trim());
                        List<string> _names = new List<string>();
                        foreach (string name in _authorsFromFile)
                        {
                            _names.Add(myTI.ToTitleCase(name.ToLower()));
                        }

                        if (_names != null)
                        {
                            foreach (string auth in _names)
                            {
                                string name = auth;
                                bool authIsEd = false;

                                if (!allAreEds)
                                {
                                    if (Regex.IsMatch(name, @"\(Ed\)"))
                                    {
                                        name = Regex.Replace(name, @"\(Ed\)", "");
                                        authIsEd = true;
                                    }
                                }
                                else
                                    name = Regex.Replace(name, @"\(Ed\)", "");

                                List<Author> _existingAuthor = Author.getByName(name);
                                if (_existingAuthor.Count == 0)
                                {
                                    Author _newAuthor = new Author();
                                    _newAuthor.Name = name;
                                    _newAuthor.Aid = Author.add(_newAuthor);

                                    if (allAreEds)
                                        Title.addAuthor(_newAuthor.Aid, newLoanable.Title.TitleId, true);
                                    else if (authIsEd)
                                        Title.addAuthor(_newAuthor.Aid, newLoanable.Title.TitleId, true);
                                    else
                                        Title.addAuthor(_newAuthor.Aid, newLoanable.Title.TitleId, false);

                                }
                                else
                                {
                                    if (allAreEds)
                                        Title.addAuthor(_existingAuthor[0].Aid, newLoanable.Title.TitleId, true);
                                    else if (authIsEd)
                                        Title.addAuthor(_existingAuthor[0].Aid, newLoanable.Title.TitleId, true);
                                    else
                                        Title.addAuthor(_existingAuthor[0].Aid, newLoanable.Title.TitleId, false);
                                }
                            }
                        }
                    }
                }

                List<Topic> titleHasTopics = Topic.getTopicsFromBook(newLoanable.Title.TitleId);
                if (titleHasTopics.Count == 0)
                {
                    string[] _topicsFromFile = Array.ConvertAll(row["Topics"].ToString().TrimEnd('?', '.', ',').Split(new Char[] { ',', '&' }), p => p.Trim());
                    foreach (string topic in _topicsFromFile)
                    {
                        string topicName = topic.Trim();
                        if (topicName != "" && topicName != " ")
                        {
                            List<Topic> _existingTopics = Topic.getByName(topicName, false);
                            if (_existingTopics.Count == 0)
                            {
                                Topic _newTopic = new Topic();
                                _newTopic.TopicName = topicName;
                                _newTopic.TopicId = Topic.add(_newTopic);
                                Title.addTopic(_newTopic.TopicId, newLoanable.Title.TitleId);
                            }
                            else
                            {
                                Title.addTopic(_existingTopics[0].TopicId, newLoanable.Title.TitleId);
                            }
                        }
                    }
                }
                if (row["Doelibs Identifier"].ToString() != "")
                {
                    if (Loanable.uniqueDoeLibSIdentifier(row["Doelibs Identifier"].ToString()))
                    {
                        newLoanable.Barcode = row["Doelibs Identifier"].ToString();
                        newReturn.ImportedWithNewID = false;
                        newReturn.DoeLibS_ID = newLoanable.Barcode;
                    }
                    else
                    {
                        string newID = generateDoeLibSIdentifier();
                        while (!Loanable.uniqueDoeLibSIdentifier(newID))
                        {
                            newID = generateDoeLibSIdentifier();
                        }

                        newLoanable.Barcode = newID;
                        newReturn.DoeLibS_ID = newLoanable.Barcode;
                        newReturn.ImportedWithNewID = true;
                    }
                }
                else
                {
                    string newID = generateDoeLibSIdentifier();
                    while (!Loanable.uniqueDoeLibSIdentifier(newID))
                    {
                        newID = generateDoeLibSIdentifier();
                    }

                    newLoanable.Barcode = newID;
                    newReturn.DoeLibS_ID = newLoanable.Barcode;
                    newReturn.ImportedWithNewID = true;
                }

                newLoanable.LoanableId = Loanable.add(newLoanable);

                newReturn.ImportedLoanable = newLoanable;
                newReturn.Imported = true;

                return newReturn;
            }
            catch
            {
                newReturn.Error = true;
                newReturn.ErrorMessage = "Failed during import. Try again or add the loanable manually.";
                return newReturn;
            }
        }

        public static async Task<ImportReturn> SuggestionToDB(JObject row, int userID)
        {
            ImportReturn newReturn = new ImportReturn();

            try
            {
                Loanable newLoanable = JsonConvert.DeserializeObject<Loanable>(row["Suggestion"]["Loanable"].ToString());
                List<Author> listAuthors = JsonConvert.DeserializeObject<List<Author>>(row["Suggestion"]["listAuthors"].ToString());
                List<Topic> listTopics = JsonConvert.DeserializeObject<List<Topic>>(row["Suggestion"]["listTopics"].ToString());
                string doeIDChanged = row["DoeLibS_ID_changed"].ToString();
                string ISBNSfield = row["Suggestion"]["ISBNS"].ToString();

                string isbnNr = Regex.Replace(ISBNSfield, "[-| ]", "");
                string[] multipleISBNS = isbnNr.Split(',');

                if(multipleISBNS.Length > 1)
                {
                    if (multipleISBNS[0].Length == 10)
                    {
                        if(newLoanable.Title.ISBN10 != multipleISBNS[0])
                            newLoanable.Title.ISBN10 = multipleISBNS[0];
                    }
                    else if (multipleISBNS[0].Length == 13)
                    {
                        if(newLoanable.Title.ISBN13 != multipleISBNS[0])
                            newLoanable.Title.ISBN13 = multipleISBNS[0];
                    }

                    if (multipleISBNS[1].Length == 10)
                    {
                        if(newLoanable.Title.ISBN10 != multipleISBNS[1])
                            newLoanable.Title.ISBN10 = multipleISBNS[1];
                    }
                    else if (multipleISBNS[1].Length == 13)
                    {
                        if(newLoanable.Title.ISBN13 != multipleISBNS[1])
                            newLoanable.Title.ISBN13 = multipleISBNS[1];
                    }
                }

                else if( isbnNr.Length == 10 || isbnNr.Length == 13)
                {
                    if (isbnNr.Length == 10 && newLoanable.Title.ISBN10 != isbnNr)
                        newLoanable.Title.ISBN10 = isbnNr;
                    else if (isbnNr.Length == 13 && newLoanable.Title.ISBN13 != isbnNr)
                        newLoanable.Title.ISBN13 = isbnNr;
                }


                Title newTitle = new Title();
                Title titleExists = null;
                List<Title> titleExists2 = null;
                if (newLoanable.Title.ISBN10 != null)
                    titleExists = Title.getByIsbn10(newLoanable.Title.ISBN10);
                if(titleExists == null && newLoanable.Title.ISBN13 != null)
                    titleExists = Title.getByIsbn13(newLoanable.Title.ISBN13);

                if (titleExists == null)
                {
                    titleExists2 = Title.getByTitle(newLoanable.Title.BookTitle, true);
                    if(titleExists2.Count == 0)
                    {
                        newTitle = newLoanable.Title;
                    }
                    else
                    {
                        newTitle = titleExists2[0];
                    }

                    if (newLoanable.Title.Publisher.PublisherId == 0)
                        newTitle.Publisher.PublisherId = Publisher.add(newLoanable.Title.Publisher);


                    List<LoanableCategory> existinCats = LoanableCategory.getByName(newLoanable.Category.Name);
                    if (existinCats.Count > 0)
                    {
                        newLoanable.Category = existinCats[0];
                    }
                    else
                    {
                        if (newLoanable.Category.CategoryId == 0)
                            newLoanable.Category.CategoryId = LoanableCategory.add(newLoanable.Category);
                    }
                    newTitle.TitleId = Title.add(newTitle);
                }
                else
                {
                    newTitle = titleExists;
                }
                

               
                newLoanable.Title = newTitle;

                List<Author> titleHasAuthors = Author.getAuthorsFromBook(newLoanable.Title.TitleId);
                if (titleHasAuthors.Count == 0)
                {
                    foreach (Author author in listAuthors)
                    {
                        if (author.Aid == 0)
                            author.Aid = Author.add(author);
                        Title.addAuthor(author.Aid, newLoanable.Title.TitleId, author.isEditor);
                    }
                }

                List<Topic> titleHasTopics = Topic.getTopicsFromBook(newLoanable.Title.TitleId);
                if (titleHasTopics.Count == 0)
                {
                    foreach (Topic topic in listTopics)
                    {
                        if (topic.TopicId == 0)
                            topic.TopicId = Topic.add(topic);
                        Title.addTopic(topic.TopicId, newLoanable.Title.TitleId);
                    }
                }
                //ADD DOEID
                if (newLoanable.Barcode != null)
                {
                    if (Loanable.uniqueDoeLibSIdentifier(newLoanable.Barcode))
                    {
                        if (doeIDChanged.ToLower() == "true")
                            newReturn.ImportedWithNewID = true;
                        newReturn.DoeLibS_ID = newLoanable.Barcode;
                    }
                    else
                    {
                        string newID = generateDoeLibSIdentifier();
                        while (!Loanable.uniqueDoeLibSIdentifier(newID))
                        {
                            newID = generateDoeLibSIdentifier();
                        }

                        newLoanable.Barcode = newID;
                        newReturn.ImportedWithNewID = true;
                        newReturn.DoeLibS_ID = newLoanable.Barcode;
                    }
                }
                else
                {
                    string newID = generateDoeLibSIdentifier();
                    while (!Loanable.uniqueDoeLibSIdentifier(newID))
                    {
                        newID = generateDoeLibSIdentifier();
                    }

                    newLoanable.Barcode = newID;
                    newReturn.ImportedWithNewID = true;
                    newReturn.DoeLibS_ID = newLoanable.Barcode;
                }

                newLoanable.LoanableId = Loanable.add(newLoanable);

                newReturn.ImportedLoanable = newLoanable;
                newReturn.Imported = true;

                return newReturn;
            }
            catch
            {
                newReturn.Error = true;
                newReturn.ErrorMessage = "Failed during import. Try again or add the loanable manually.";
                return newReturn;
            }
        }



    }
}
